package com.edu.hre.db.sql.pim;

import java.io.Serializable;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

import com.edu.hre.common.utils.CommonUtils;
import com.edu.hre.db.pojo.HreEmpJobs;
import com.edu.hre.db.service.BaseService;
import com.edu.hre.db.utils.Compare;
import com.edu.hre.struts.form.pim.EmpJobForm;
import com.edu.hre.struts.form.pim.EmpJobListForm;

public class EmpJobDAOService implements Serializable {
	
	/**
	 * 
	 */
	private static final long serialVersionUID = 3394301416150421253L;
	public static final String BEAN_NAME = "empJobDAOService";
	private BaseService baseService  = null;
	 
	public void setBaseService(BaseService baseService) {
		this.baseService = baseService;
	} 

	public ArrayList<EmpJobForm> getEmpJobList(int limit, int offset, int empNumber){

		ArrayList<EmpJobForm> listResult = new ArrayList<EmpJobForm>();

		String sql = "SELECT ejobs.id AS col1," +
					" tit.job_title AS col2," +
					" cat.name AS col3," +
					" sta.name AS col4, " +
					" ejobs.joined_date AS col5, " +
					" uni.name AS col6, " +
					" loc.name AS col7, " +
					" ejobs.terminated_status AS col8, " +
					" ter.name AS col9, " +
					" ejobs.contract_start_date AS col10, " +
					" ejobs.contract_end_date AS col11 " +

					" FROM hre_emp_jobs AS ejobs" +
					" LEFT OUTER JOIN hre_employee AS emp ON ejobs.emp_number = emp.emp_number" +
					" LEFT OUTER JOIN hre_job_title AS tit ON ejobs.job_title_id = tit.id " +
					" LEFT OUTER JOIN hre_job_category AS cat ON ejobs.job_category_id = cat.id " +
					" LEFT OUTER JOIN hre_employment_status AS sta ON ejobs.emp_status_id = sta.id " +
					" LEFT OUTER JOIN hre_subunit AS uni ON ejobs.sub_unit_id = uni.id " +
					" LEFT OUTER JOIN hre_location AS loc ON ejobs.location_id = loc.id " +
					" LEFT OUTER JOIN hre_emp_termination_reason AS ter ON ejobs.termination_reason_id = ter.id " +

					" WHERE emp.is_deleted = 0 " +
					" AND emp.emp_number = ? " +
					" ORDER BY ejobs.date_created DESC";

		List <Object[]> listObject = baseService.listArrayObject(sql, limit, offset, empNumber);
		for(int i = 0; listObject != null && i < listObject.size(); i++){

			try {

				Object[] objs = listObject.get(i);
				EmpJobForm job = new EmpJobForm();
	
				job.setId(Integer.parseInt(CommonUtils.normalizeObjectToString(objs[1], "0")));
				job.setEmpNumber(empNumber);
				
				job.setJobTitle(CommonUtils.normalizeObjectToString(objs[2], ""));
				job.setEmploymentStatus(CommonUtils.normalizeObjectToString(objs[4], ""));
				job.setJoinedDate(CommonUtils.dateToString((Date) objs[5]));
				job.setSubUnit(CommonUtils.normalizeObjectToString(objs[6], ""));
				job.setLocation(CommonUtils.normalizeObjectToString(objs[7], ""));
				
				if (objs[8] == null || objs[8].toString().trim().equals("")
						|| objs[8].toString().trim().equals("false")) {
					job.setTerminated("");
					job.setTerminatedStatus(false);
				} else {
					job.setTerminated(CommonUtils.normalizeObjectToString(objs[9], ""));
					job.setTerminatedStatus(true);
				}
				
				job.setFrom(CommonUtils.dateToString((Date) objs[10]));
				job.setTo(CommonUtils.dateToString((Date) objs[11]));

				listResult.add(job);
			} catch (Exception e) {
				CommonUtils.println(e.getMessage());
			}
		}

		return listResult;
	}

	public int countEmpJobs(int empNumber){

		int count = baseService.countByColumns(new String[]{"empNumber"}, new int[]{Compare.EQUAL}, new Integer[]{empNumber}, "id", HreEmpJobs.class);
		return count < 0 ? 0 : count;
	}

	public void deleteJobs(EmpJobListForm form){

		ArrayList<EmpJobForm> info = (ArrayList<EmpJobForm>) form.getAllJobs();

		for (int i = 0; i < info.size(); i++) {
			EmpJobForm work = info.get(i);
			if (work.isSelected()) {
				baseService.deleteFirstPojo("id", Compare.EQUAL, (long) work.getId(), null, null, 0, HreEmpJobs.class);
			}
		}
	}
}
